﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using syc.Helper;
using System.Drawing;

namespace syc.Sale.Report
{
    public partial class 油管销售日报表 : System.Web.UI.Page
    {

        private string sql = @"SELECT 
d.名称 AS 接货单位,
COUNT(c.日期) AS 当日车数,
SUM(c.总支数) AS 当日支数, 
SUM(c.总重量)/1000 AS 当日重量, 
	(SELECT COUNT(*) AS Expr1
	FROM dbo.产品出库单
	WHERE  日期 <= '*日期*' AND 
	       日期 >=(SELECT [起始日期] FROM dbo.[生产月设置] WHERE [分厂]=0 AND [年度]=*年份* AND [月份]=*月份* ) AND
		   订货单位ID=d.ID AND 产品类别ID=*产品类别*           
		   ) 
		   AS 月车数,
	(SELECT SUM(总支数) AS Expr1
	FROM dbo.产品出库单
	WHERE  日期 <= '*日期*' AND
	       日期 >=(SELECT [起始日期] FROM dbo.[生产月设置] WHERE [分厂]=0 AND [年度]=*年份* AND [月份]=*月份* ) AND
		   订货单位ID=d.ID AND 产品类别ID=*产品类别*         
		   ) 
		   AS 月支数,
	(SELECT SUM(总重量) AS Expr1
	FROM dbo.产品出库单
	WHERE  日期 <= '*日期*' AND
           日期 >=(SELECT [起始日期] FROM dbo.[生产月设置] WHERE [分厂]=0 AND [年度]=*年份* AND [月份]=*月份* ) AND
		   订货单位ID=d.ID AND 产品类别ID=*产品类别*           
		   )/1000 
		   AS 月重量,
	(SELECT COUNT(*) AS Expr1
	FROM dbo.产品出库单
	WHERE  日期 <= '*日期*' AND
           日期 >=(SELECT [起始日期] FROM dbo.[生产月设置] WHERE [分厂]=0 AND [年度]=*年份* AND [月份]=1 ) AND
		   订货单位ID=d.ID AND 产品类别ID=*产品类别*          
		   ) 
		   AS 年车数,
	(SELECT SUM(总支数) AS Expr1
	FROM dbo.产品出库单
	WHERE  日期 <= '*日期*' AND
		   日期 >=(SELECT [起始日期] FROM dbo.[生产月设置] WHERE [分厂]=0 AND [年度]=*年份* AND [月份]=1 ) AND
		   订货单位ID=d.ID AND 产品类别ID=*产品类别*            
		   ) 
		   AS 年支数,
	(SELECT SUM(总重量) AS Expr1
	FROM dbo.产品出库单
	WHERE  日期 <= '*日期*' AND
		   日期 >=(SELECT [起始日期] FROM dbo.[生产月设置] WHERE [分厂]=0 AND [年度]=*年份* AND [月份]=1 ) AND
		   订货单位ID=d.ID  AND 产品类别ID=*产品类别*     
		   )/1000 
		   AS 年重量
FROM 
dbo.产品出库单 AS c RIGHT JOIN
dbo.产品订货单位 AS d ON d.ID = c.订货单位ID
AND c.日期='*日期*' AND c.产品类别ID=*产品类别*
GROUP BY
d.名称,
d.ID";
        protected static DateTime date;


        protected void Page_Load(object sender, EventArgs e)
        {

            this.Message.Text = "·准备就绪";
            if (!this.IsPostBack)
            {
                bool ifsuccedParsed = DateTime.TryParse(RiQi.Text, out date);

                //如果解析成功则使用，不成功则使用当天的时间
                if (ifsuccedParsed)
                {

                }
                else
                {
                    date = DateTime.Today;
                }
                RiQi.Text = date.ToString("yyyy年MM月dd日");
                sql = sql.Replace("*日期*", date.ToString("yyyy-MM-dd"));
                sql = sql.Replace("*年份*", date.Year.ToString());
                sql = sql.Replace("*月份*", date.Month.ToString());
                //给产品类别一个默认值1
                if (ChanPin.SelectedValue !="" )
                {
                    sql = sql.Replace("*产品类别*", ChanPin.SelectedValue);
                    }
                else{
                    sql = sql.Replace("*产品类别*", "1");
                }
                this.SqlDataSource1.SelectCommand = sql;
                this.ViewState["SelectCommand"] = sql;
            }
            this.SqlDataSource1.SelectCommand = (string)this.ViewState["SelectCommand"];
        }


        //通用设置GRIDLINES颜色的代码
        protected void MyGrid_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            foreach (TableCell tc in e.Row.Cells)
            {
                tc.Attributes["style"] = "border-color:black";
            }
        }

        protected void RiQi_TextChanged(object sender, EventArgs e)
        {
            bool ifsuccedParsed = DateTime.TryParse(RiQi.Text, out date);

            //如果解析成功则使用，不成功则使用当天的时间
            if (ifsuccedParsed)
            {

            }
            else
            {
                date = DateTime.Today;
            }
            sql = sql.Replace("*日期*", date.ToString("yyyy-MM-dd"));
            sql = sql.Replace("*年份*", date.Year.ToString());
            sql = sql.Replace("*月份*", date.Month.ToString());
            this.SqlDataSource1.SelectCommand = sql;
            this.ViewState["SelectCommand"] = sql;

            this.SqlDataSource1.SelectCommand = (string)this.ViewState["SelectCommand"];
        }


        //设置表头
        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {

            switch (e.Row.RowType)
            {
                case DataControlRowType.Header:
                    //第一行表头
                    TableCellCollection tcHeader = e.Row.Cells;
                    tcHeader.Clear();
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[0].Attributes.Add("rowspan", "2"); //跨2Row
                    tcHeader[0].Text = "接货单位";
                    tcHeader[0].Height = 80;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[1].Attributes.Add("colspan", "3"); //跨3Col
                    tcHeader[1].Text = "当日数据";
                    tcHeader[1].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[2].Attributes.Add("colspan", "3"); //跨3Col
                    tcHeader[2].Text = "当月数据";
                    tcHeader[2].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[3].Attributes.Add("colspan", "3"); //跨3Col
                    tcHeader[3].Text = "当年数据</th></tr><tr>";
                    tcHeader[3].Height = 40;

                    //第二行表头
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[4].Attributes.Add("colspan", "1");
                    tcHeader[4].Text = "车数";
                    tcHeader[4].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[5].Attributes.Add("colspan", "1");
                    tcHeader[5].Text = "支数";
                    tcHeader[5].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[6].Attributes.Add("colspan", "1");
                    tcHeader[6].Text = "重量(吨)";
                    tcHeader[6].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[7].Attributes.Add("colspan", "1");
                    tcHeader[7].Text = "车数";
                    tcHeader[7].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[8].Attributes.Add("colspan", "1");
                    tcHeader[8].Text = "支数";
                    tcHeader[8].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[9].Attributes.Add("colspan", "1");
                    tcHeader[9].Text = "重量(吨)";
                    tcHeader[9].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[10].Attributes.Add("colspan", "1");
                    tcHeader[10].Text = "车数";
                    tcHeader[10].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[11].Attributes.Add("colspan", "1");
                    tcHeader[11].Text = "支数";
                    tcHeader[11].Height = 40;
                    tcHeader.Add(new TableHeaderCell());
                    tcHeader[12].Attributes.Add("colspan", "1");
                    tcHeader[12].Text = "重量(吨)</th></tr><tr>";
                    tcHeader[12].Height = 40;

                    break;

            }

        }

        protected void Button_New_Click(object sender, EventArgs e)
        {
            bool ifsuccedParsed = DateTime.TryParse(RiQi.Text, out date);

            //如果解析成功则使用，不成功则使用当天的时间
            if (ifsuccedParsed)
            {

            }
            else
            {
                date = DateTime.Today;
            }
            sql = sql.Replace("*日期*", date.ToString("yyyy-MM-dd"));
            sql = sql.Replace("*年份*", date.Year.ToString());
            sql = sql.Replace("*月份*", date.Month.ToString());
            sql = sql.Replace("*产品类别*",ChanPin.SelectedValue);
            this.SqlDataSource1.SelectCommand = sql;
            this.ViewState["SelectCommand"] = sql;

            this.SqlDataSource1.SelectCommand = (string)this.ViewState["SelectCommand"];
        }

        //导出按钮
        protected void Button1_Click(object sender, EventArgs e)
        {
            GridView1.DataBind();
            //var ss = GridViewExportHelper.GetExportGridContent(GridView1, false,false);
            //GridViewExportHelper.WriteResponse(ss, "application/vnd.ms-excel", true, "Filename.xls");
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
            Response.Charset = "utf-8";
            // If you want the option to open the Excel file without saving than
            // comment out the line below
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.xls";
            //设置EXCEL的表头信息
            var title = "销售日报表";
            HttpContext.Current.Response.Write(
    @"<!DOCTYPE html PUBLIC ""-//W3C//DTD XHTML 1.0 Transitional//EN"" ""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"">
<html>
<head>
<meta http-equiv=""Content-Language"" content=""zh-cn""/>
<meta http-equiv=""Content-Type"" content="""
    + HttpContext.Current.Response.ContentType + "; charset=utf-8\"/></head><body><h2><center>" + title + "</center></h2>");
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
            GridView1.RenderControl(htmlWrite);
            Response.Write(stringWrite.ToString());
            HttpContext.Current.Response.Write("</body></html>");
            Response.End();

        }

        //为了导出而重载的一个事件，具体看EXPORT TO EXCEL的文章
        public override void VerifyRenderingInServerForm(Control control)
        {

            // Confirms that an HtmlForm control is rendered for the
            //specified ASP.NET server control at run time.

        }


    }





}
